--member
create table safari_member(
	id varchar2(100) primary key,
	password varchar2(100) not null,
	name varchar2(100) not null,
	address varchar2(500) not null,
	birthday number not null,
	gender varchar2(100) not null,
	cellphone varchar2(100) not null,
	email varchar2(100) unique,
	grade varchar2(100) not null,
	point number not null
)

--사진 올리는거 때문에 추가
alter table safari_member add(saveFileName varchar2(100));
alter table safari_member add(orgFileName varchar2(100));

--회원가입
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('test1','1','여정','BUSAN',19900908,'female','01093879027','duwjddlek@naver.com','admin',5000);
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('duwjddlek','1','여정','BUSAN',19900908,'female','01093879027','duwjddlek@naver.com','customer',5000);
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('audrlek','1','명기','YONGIN',19871226,'male','01049381326','audrlek@naver.com','customer',5000);
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('admin','1','관리자','BUSAN',19900908,'female','01093879027','duwjddlek@naver.co','admin',5000);

--확인용
select * from SAFARI_MEMBER;
select * from SAFARI_MEMBER WHERE ID='admin';

--회원탈퇴
delete from safari_member where id='v';

--회원정보 수정
update SAFARI_MEMBER set name='ettto',address='efwfwe' where id='et'

--쪽지함 생성
create table safari_message(
message_no number primary key,
senderId varchar2(100) not null,
receiverId varchar2(100) not null,
message_content varchar2(2000) not null,
send_date date not null,
constraint fk_senderId foreign key(senderId) references safari_member(id)
)
alter table safari_message drop(didRead);

--아래꺼가 진짜
alter table safari_message add(didRead varchar2(50) default 'x')

--쪽지함의 message_no 용 sequence
create sequence seq_message;

--새 쪽지 보내기(insert)
insert into safari_message(message_no,senderId,receiverId,message_content,send_date) values(seq_message.nextval,'sdf','dsddf','this is test content',sysdate);

--쪽지함 확인용
select * from safari_message;

--내 받은 쪽지 select
select * from safari_message where senderId='et'

--받는 사람 id 존재여부 확인
select count(*) from SAFARI_MEMBER where id='dsf'

--받은 메세지 갯수
select count(*) from safari_message where receiverid='dsf'

--받은 메세지 가져오기
select message_no,senderId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date from SAFARI_MESSAGE where receiverId='dsf'

-- 받은 메세지 해당 페이지별로 가져오기
select  message_no,senderId,message_content,send_date from (
select message_no,senderId,message_content,send_date,
ceil(rownum/10) as page from (
select  message_no,senderId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date
from safari_message where receiverId='dsf'
order by message_no desc
)
)where page=1


--확인용
	   select message_no,senderId,receiverId,message_content,send_date from (
select message_no,senderId,receiverId,message_content,send_date,
ceil(rownum/10) as page from (
select  message_no,senderId,receiverId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date
from safari_message where receiverId='dsf'
order by message_no desc
)
)where page='1'

select trade_history_no as tradeHistoryNo,time_traded as timeTraded, trade_buyer as buyer, trade_seller as seller from SAFARI_TRADE_HISTORY where trade_buyer='et'

select didread from safari_message where message_no=31

update safari_message set didread='true' where message_no=31 and receiverId='et'

select count(*) from safari_message where didread='x' and receiverId='et'

select count(*) from safari_member where email='a'

--보낸사람 아이디로 메세지 검색
select * from safari_message where receiverId='et' and senderId='dsf'

   select senderId,receiverId,message_content as content,send_date,didRead from(
	    select senderId,receiverId,message_content,send_date,didRead,ceil(rownum/10) as page from(
	    select senderId,receiverId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date,didRead
	     from safari_message where receiverId='et' and senderId='dsf' order by send_date desc))
	     where page=1
	     
-- 내 판매내역 갯수
	     select count(*) from safari_trade_history where trade_buyer='et'
	     
	  select trade_history_no as tradeHistoryNo,time_traded as timeTraded, trade_buyer as buyer, trade_seller as seller,sell_title,sell_product_name,sell_product_price
 	from(
 	select th.trade_history_no, th.time_traded,th.sell_no,th.trade_buyer,th.trade_seller,s.sell_title,s.sell_product_name,s.sell_product_price, ceil(rownum/10) as page
 	from safari_sellboard s, safari_trade_history th
 	where th.sell_no=s.sell_no and th.trade_buyer='et'
 	order by trade_history_no desc
 	)
 	where page='1'     
 	
 	
 	------------------자기가 쓴 글 있어도 글 삭제 가능하게-------------------
 	
-- (1) safari_message : 외래키 제약조건 제거
alter table safari_message drop constraint fk_senderId;

--constraint fk_senderId foreign key(senderId) references safari_member(id)

-- (2) safari_message : 외래키 제약조건 추가
alter table safari_message 
add constraint fk_senderId foreign key(senderId) 
references safari_member(id) 
on delete cascade;

